import scrapy
import xlwt, xlrd
import time
import pandas as pd
from datetime import datetime


# scrapy runspider myspider.py  运行指令

class ReadExcel(object):
    def __init__(self, data_path, sheetname):
        # 定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows

    def readExcel(self):
        for i in range(1, self.rowNum):
            # 获取单元格数据
            result = self.table.cell_value(i, 2)
            if result == 'PASS' or result == 'pass':
                pass

            endTime = self.table.cell_value(i, 5)
            dt = datetime.strptime(endTime, "%Y/%m/%d %H:%M:%S")
            timestamp = int(dt.timestamp()) * 1000

            print(timestamp - asrIntentTime)
            # print(contents)


class WriteExcel():
    def __init__(self, sheet_name, excel_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)
        self.excel_name = excel_name

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save(
            '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(
                self.excel_name, current_time))


class MySpider(scrapy.Spider):
    name = 'myspider'
    start_urls = ['file:///Users/v_baiguanghui/Downloads/%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%901-2.html', ]

    def parse(self, response):

        writeObj = WriteExcel('Sheet', '数据分析')
        writeObj.write_excel_body(0, 0, '指令')
        writeObj.write_excel_body(0, 1, 'expChart')
        writeObj.write_excel_body(0, 2, 'expSql')
        writeObj.write_excel_body(0, 3, 'Chart')
        writeObj.write_excel_body(0, 4, 'Sql')
        writeObj.write_excel_body(0, 5, 'TTS')
        writeObj.write_excel_body(0, 6, '结论')
        writeObj.write_excel_body(0, 7, '备注')

        links = response.css('body > div > details').getall()
        print(len(links), type(links))

        def acquireRightResult():
            dataPath = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/history/端到端-23.11.22/'
            sheetName = '准确率'
            obj = ReadExcel(dataPath, sheetName)
            rightData = []

            for count in range(1, len(links[0] + 1)):
                pass

            return rightData

        def verifyResult():
            verifedData = []
            pass

        for count in range(1, len(links) + 1):
            divNum = response.css('body > div > details:nth-child({}) > dl > div'.format(count)).getall()

            # 是否为标准输出：7为标准输出的行数
            if len(divNum) == 7:
                cmd = response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get().split('：')[
                    1]
                cmd1 = response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get()
                if cmd1 == "指令：" or cmd1.startswith("指令：{"):  # 语音输入为空
                    continue
                else:
                    midWareReturn = response.css(
                        'body > div > details:nth-child({}) > dl > div:nth-child(4) > dd > pre::text'.format(
                            count)).get()
                    index_type = midWareReturn.find('type')
                    index_typeEnd = midWareReturn.find('"', index_type + 8)
                    if midWareReturn[index_type + 8:index_typeEnd] == '数据分析':
                        notNull = response.css(
                            'body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'.format(
                                count)).get()
                        print(notNull)
                        if notNull:
                            asrTextInputTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(1) > dt::text'.format(
                                    count)).get().split('- ')[1]
                            wsRevAsrTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(2) > dt::text'.format(
                                    count)).get().split('- ')[1]
                            asrIntentTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(2) > dd > pre::text'.format(
                                    count)).get().split('"time": ')[1][0:13]
                            asrStartReqTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(3) > dt::text'.format(
                                    count)).get().split('- ')[1]
                            midWareReturn = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(4) > dd > pre::text'.format(
                                    count)).get()

                            reqAnalysisTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(5) > dt::text'.format(
                                    count)).get().split('- ')[1]
                            resAnalysisTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(6) > dt::text'.format(
                                    count)).get().split('- ')[1]
                            resAnalysisContent = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'.format(
                                    count)).get()
                            endTime = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(7) > dt::text'.format(
                                    count)).get().split('- ')[1]
                            # print(cmd,endTime)
                            # file.write(cmd + '\n' + asrTextInputTime + '\n'+wsRevAsrTime + '\n' + asrIntentTime +'\n' + asrStartReqTime+ '\n' +midWareReturn+'\n'+endTime+'\n')
                            # file.write(cmd+'\n')

                            index_reqPars = midWareReturn.find('请求意图参数",')
                            if index_reqPars != -1:
                                index_reqPars_time = midWareReturn.find('timestamp', index_reqPars)
                                reqIntent_time = midWareReturn[index_reqPars_time + 13:index_reqPars_time + 26:1]
                                print('请求意图参数：', reqIntent_time)
                            else:
                                print('请求意图参数：空')

                            index_resInent = midWareReturn.find('意图识别返回",')
                            if index_resInent != -1:
                                index_resInent_time = midWareReturn.find('timestamp', index_resInent)
                                resIntent_time = midWareReturn[index_resInent_time + 13:index_resInent_time + 26:1]
                                print('意图识别返回：', resIntent_time)
                            else:
                                print('意图识别返回：空')

                            index_reqAnalyPars = resAnalysisContent.find('请求数据分析参数",')
                            if index_reqAnalyPars != -1:
                                index_reqAnalyPars_time = resAnalysisContent.find('timestamp', index_reqAnalyPars)
                                reqAnalyPars_time = resAnalysisContent[
                                                    index_reqAnalyPars_time + 13:index_reqAnalyPars_time + 26:1]
                                print('请求数据分析参数：', reqAnalyPars_time)
                            else:
                                print('请求数据分析参数：空')

                            index_reqGenSql = resAnalysisContent.find('请求生成SQL",')
                            if index_reqGenSql != -1:
                                index_reqGenSql_time = resAnalysisContent.find('timestamp', index_reqGenSql)
                                reqGenSql_time = resAnalysisContent[
                                                 index_reqGenSql_time + 13:index_reqGenSql_time + 26:1]
                                print('请求生成sql：', reqGenSql_time)
                            else:
                                print('请求生成sql：空')

                            index_resSql = resAnalysisContent.find('生成SQL返回",')
                            if index_resSql != -1:
                                index_resSql_time = resAnalysisContent.find('timestamp', index_resSql)
                                resSql_time = resAnalysisContent[index_resSql_time + 13:index_resSql_time + 26:1]
                                print('生成SQL返回：', resSql_time)
                            else:
                                print('生成SQL返回：空')

                            index_execSql = resAnalysisContent.find('执行的SQL语句",')
                            if index_execSql != -1:
                                index_execSql_time = resAnalysisContent.find('timestamp', index_execSql)
                                execSql_time = resAnalysisContent[index_execSql_time + 13:index_execSql_time + 26:1]
                                print('执行的SQL语句：', execSql_time)
                            else:
                                print('执行的SQL语句：空')

                            index_execSql = resAnalysisContent.find('SQL执行结果返回条数",')
                            if index_execSql != -1:
                                index_execSql_time = resAnalysisContent.find('timestamp', index_execSql)
                                reqChart_time = resAnalysisContent[index_execSql_time + 13:index_execSql_time + 26:1]
                                resChart_time = reqChart_time  # 如果sql执行结果返回0，将生成图表时间配置为0
                                print('SQL执行结果返回条数', reqChart_time)

                            index_reqChart = resAnalysisContent.find('请求图表分析",')
                            if index_reqChart != -1:
                                index_reqChart_time = resAnalysisContent.find('timestamp', index_reqChart)
                                reqChart_time = resAnalysisContent[index_reqChart_time + 13:index_reqChart_time + 26:1]
                                print('请求图表分析：', reqChart_time)
                            else:
                                print('请求图表分析：空')

                            index_resChart = resAnalysisContent.find('图表分析返回",')
                            if index_resChart != -1:
                                index_resChart_time = resAnalysisContent.find('timestamp', index_resChart)
                                resChart_time = resAnalysisContent[index_resChart_time + 13:index_resChart_time + 26:1]
                                print('图表分析返回：', resChart_time)
                            else:
                                print('图表分析返回：空')

                            index_resTime = resAnalysisContent.find('开始响应",')
                            if index_resTime != -1:
                                index_res_time = resAnalysisContent.find('timestamp', index_resTime)
                                res_time = resAnalysisContent[index_res_time + 13:index_res_time + 26:1]
                                print('开始响应：', res_time)
                            else:
                                print('开始响应：空')

                            dt = datetime.strptime(asrTextInputTime, "%Y/%m/%d %H:%M:%S")
                            timestamp = int(dt.timestamp()) * 1000

                            asrToFrontCost = int(timestamp) - int(asrIntentTime)
                            FrontToReqCost = int(reqIntent_time) - int(timestamp)
                            intentCost = int(resIntent_time) - int(reqIntent_time)
                            genSqlCost = int(resSql_time) - int(reqGenSql_time)
                            execSqlCost = int(reqChart_time) - int(execSql_time)
                            genChartCost = int(resChart_time) - int(reqChart_time)
                            totalCost = int(res_time) - int(asrIntentTime)

                            writeObj.write_excel_body(count, 0, cmd)
                            writeObj.write_excel_body(count, 1, asrTextInputTime)
                            writeObj.write_excel_body(count, 2, wsRevAsrTime)
                            writeObj.write_excel_body(count, 3, asrIntentTime)
                            writeObj.write_excel_body(count, 4, asrStartReqTime)
                            writeObj.write_excel_body(count, 5, reqIntent_time)
                            writeObj.write_excel_body(count, 6, resIntent_time)
                            writeObj.write_excel_body(count, 7, reqAnalyPars_time)
                            writeObj.write_excel_body(count, 8, reqGenSql_time)
                            writeObj.write_excel_body(count, 9, resSql_time)
                            writeObj.write_excel_body(count, 10, execSql_time)
                            writeObj.write_excel_body(count, 11, reqChart_time)
                            writeObj.write_excel_body(count, 12, resChart_time)
                            writeObj.write_excel_body(count, 13, res_time)
                            writeObj.write_excel_body(count, 14, endTime)
                            writeObj.write_excel_body(count, 15, asrToFrontCost)
                            writeObj.write_excel_body(count, 16, FrontToReqCost)
                            writeObj.write_excel_body(count, 17, intentCost)
                            writeObj.write_excel_body(count, 18, genSqlCost)
                            writeObj.write_excel_body(count, 19, execSqlCost)
                            writeObj.write_excel_body(count, 20, genChartCost)
                            writeObj.write_excel_body(count, 21, totalCost)
                            writeObj.write_excel_body(count, 22, midWareReturn)
                            writeObj.write_excel_body(count, 23, resAnalysisContent[0:32766])
            else:
                pass
        writeObj.save_excel()


if __name__ == '__main__':
    import sys

    if len(sys.argv) > 1:
        start_url = sys.argv[1]
    else:
        start_url = 'file:///Users/v_baiguanghui/Downloads/aaa.html'

    spider = MySpider()
    spider.start_urls = [start_url]
    spider.run()
